/**
 * author wen
 * cunyu
 * email wjd_13@hotmail.com
 */
package com.cunyu.service;

import cn.tom.kit.StringUtil;
import com.cunyu.bean.Muser;
import com.cunyu.bean.OrderPool;
import com.cunyu.bean.ReqData;
import com.cunyu.bean.RespData;
import com.cunyu.dao.DBQuery;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;

/**
 * 池子
 *
 * @author win
 * 2019/04/24-13:38
 */
@Service
public class OrderPoolService extends BaseService{

    public RespData<List<Map<String, Object>>> getOrders(ReqData reqData,
                                                         OrderPool pool, Muser muser) {
        //select uid,(case when source = 0 then
        //  (select m.uname from muser m where m.id = uid) when source = 1 then
        //  (select s.name from userinfo s where s.uid = uid) end) sourceuname
        //from orderpool ;
        String sql = "select p.id,p.source,p.ctime::date,p.name,p.tel,p.age,p.loan_amount,p.loan_period,p.loan_use" +
                ",p.city, p.state,p.qcode," +
                "(case when p.source = 0 then (select m.uname from muser m where m.id = p.uid) when p.source = 1 then " +
                "(select s.name from userinfo s where s.uid = p.uid) end) sourceuname"+
                " from orderpool p where 1=1 " ;
        DBQuery query = new DBQuery(sql);
        if(muser.getRole() != 1){
            query.add(" and ((p.source = 0 and p.uid = ? ) or (p.qcode = (select m.qcode from muserinfo m where m.muid = ? ) )) ");
            query.setParams(muser.getId()) ;
            query.setParams(muser.getId());
        }
        if(StringUtil.hasLength(pool.getName())){
            query.and(" p.name like ? ").setParams("%"+pool.getName()+"%") ;
        }
        if(pool.getTel() != null){
            query.and( " p.tel = ? ").setParams(pool.getTel()) ;
        }
        if(pool.getSource() != null){
            query.and(" p.source = ? ").setParams(pool.getSource());
        }
        if(StringUtil.hasLength(pool.getCity())){
            query.and(" p.city like ? ").setParams("%"+pool.getCity()+"%") ;
        }
        if(pool.getHascar() != null){
            query.and(" p.hascar = ?").setParams(pool.getHascar()) ;
        }
        if(pool.getHashouse() != null){
            query.and(" p.hashouse = ?").setParams(pool.getHashouse()) ;
        }
        if(pool.getSocial_security() != null){
            query.and(" p.social_security = ?").setParams(pool.getSocial_security()) ;
        }
        if(pool.getProvident_fund() != null){
            query.and(" p.provident_fund = ? ").setParams(pool.getProvident_fund()) ;
        }
        if(pool.getHaslife() != null){
            query.and(" p.haslife = ?").setParams(pool.getHaslife()) ;
        }
        if(pool.getHaswld() != null){
            query.and(" p.haswld = ?").setParams(pool.getHaswld()) ;
        }

        return page(query.getSql(),reqData,query.getParams()) ;
    }

    public Map<String, Object> getOrder(Integer id) {
        String sql = "select p.*,(case when p.source = 0 then (select m.uname from muser m where m.id = p.uid) " +
                " when p.source = 1 then" +
                " (select s.name from userinfo s where s.uid = p.uid) end) sourceuname," +
                " (select c.pcode from codecity c where p.citycode = c.code) pcode ," +
                "(select r.id from myorder r where r.oid = p.id) moid," +
                "(select r.state from myorder r where r.oid = p.id) mstate from orderpool p where p.id = ? ";
        return getMap(sql,id) ;
    }

    public int newPool(OrderPool pool) {
        String count = "select count(*) from orderpool where tel = ? " ;
        int v = getInt(count,pool.getTel());
        if(v> 0){
            return -1 ;
        }
        /*String sql = "insert into orderpool ( source, uid, state, type, ctime, name, tel, age," +
                " address, loan_time, loan_amount, loan_period, loan_use," +
                " citycode, census, social_security, provident_fund, job," +
                " income, incometype, creditcard, haswld, hashouse," +
                " hascar, haslife, city, hq, discount,carpawn,housepawn,sex)" +
                " values (?,?,?,?,?,?,?,?," +
                "?,?,?,?,?," +
                "?,?,?,?,?," +
                "?,?,?,?,?," +
                "?,?,?,?,?,?,?,?) " ;

        //super.insertDB()
        insert(sql,intV(pool.getSource()),intV(pool.getUid()),0,intV(pool.getType()),
                new Date(),pool.getName(),Long.valueOf(pool.getTel()),intV(pool.getAge()),pool.getAddress(),
                dateV(pool.getLoan_time()), doubleV(pool.getLoan_amount()),intV(pool.getLoan_period()),intV(pool.getLoan_use()),intV(pool.getCitycode()),
                intV(pool.getCensus()),intV(pool.getSocial_security()),intV(pool.getProvident_fund()),
                intV(pool.getJob()),intV(pool.getIncome()),intV(pool.getIncometype()),intV(pool.getCreditcard()),
                intV(pool.getHaswld()),intV(pool.getHashouse()),intV(pool.getHascar()),
                intV(pool.getHaslife()),pool.getCity(),intV(pool.getHq()),doubleV(pool.getDisount()),
                intV(pool.getCarpawn()),intV(pool.getHousepawn()),intV(pool.getSex())) ;*/
        //orderpool
        int score = pool.hqScore();
        if(score >= 2)
            pool.setHq(1);
        pool.setPrice(pool.orderMoney(score));
        return insertDB(pool,"orderpool");
    }

    public void updatePool(OrderPool pool ,int id) {
        /*String sql = "update orderpool set source=?, uid=?, type=?, name=?, tel=?, age=?," +
                " address=?, loan_time=?, loan_amount=?, loan_period=?, loan_use=?," +
                " citycode=?, census=?, social_security=?, provident_fund=?, job=?," +
                " income=?, incometype=?, creditcard=?, haswld=?, hashouse=?," +
                " hascar=?, haslife=?, city=?, hq=?, discount=?,carpawn = ?," +
                "housepawn =?,sex=? where id = ? and state = 0" ;
        exec(sql,intV(pool.getSource()),intV(pool.getUid()),intV(pool.getType()),
                pool.getName(),Long.valueOf(pool.getTel()),intV(pool.getAge()),pool.getAddress(),
                dateV(pool.getLoan_time()), doubleV(pool.getLoan_amount()),intV(pool.getLoan_period()),
                intV(pool.getLoan_use()),intV(pool.getCitycode()),
                intV(pool.getCensus()),intV(pool.getSocial_security()),intV(pool.getProvident_fund()),
                intV(pool.getJob()),intV(pool.getIncome()),intV(pool.getIncometype()),intV(pool.getCreditcard()),
                intV(pool.getHaswld()),intV(pool.getHashouse()),intV(pool.getHascar()),
                intV(pool.getHaslife()),pool.getCity(),intV(pool.getHq()),doubleV(pool.getDisount()),
                intV(pool.getCarpawn()),intV(pool.getHousepawn()), intV(pool.getSex()),intV(pool.getId())) ;*/
        int score = pool.hqScore();
        if(score >= 2)
            pool.setHq(1);
        int i = pool.orderMoney(score);
        pool.setPrice(i);
        updateDB(pool,"orderpool"," id = "+id) ;
    }

    //....
    //@Transactional(rollbackFor = Exception.class)
    public int importData(List<Object[]> orderPools,Muser u) {
        String info = "insert into orderPool (uid,name,age,sex,tel,city,loan_amount," +
                "loan_period,loan_use,census,social_security,provident_fund,job,income,incometype," +
                "creditcard,haswld,hashouse,hascar,haslife,carpawn,housepawn,hq,price,citycode,zmscore,credit) values " +
                "("+u.getId()+",?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)" ;
        batchUpdate(info,orderPools) ;

        /*
        此sql 有问题  不能正常获取到 citycode
        String day = BaseUtil.getTodayStr();
        String code = "update orderpool p set citycode = (select max(c.code) from codecity c where c.name like concat('%',p.citycode,'%') ) " +
                "where p.citycode is null and ctime > '"+day+"' " ;
        exec(code) ;*/
        return 0 ;
    }

    public void verfiy(Integer id) {
        String sql = "update orderPool set state = 0 where id = ? and state = -1 " ;
        exec(sql,id) ;
    }

    public int telCount(Object tel) {
        String sql = "select count(*) from orderPool where tel = ? " ;
        return getInt(sql,tel) ;
    }

    public Object cityCode(Object city) {
        String sql = "select max(c.code) from codecity c where c.name like ? " ;
        return getInt (sql,"%"+city.toString()+"%");
    }

    public List<Map<String, Object>> pushOrders(){
        String sql = "select * from orderPool where state = 0 and ctime >= 'today'" ;
        return  getList(sql) ;
    }

    public void setCityCode(OrderPool pool) {
        String sql = "select min(code) as city ,count(*) size from codeCity where name like ?" ;
        Map<String, Object> map = getMap(sql, "%" + pool.getCity() + "%");
        if((long)map.get("size") != 1){
            return;
        }
        pool.setCitycode( (int)map.get("city"));
    }

    public int checkQcode(OrderPool pool) {
        String sql = "select count(*)::int from mUserInfo where qCode = ? " ;
        Integer qc = getInt(sql, pool.getQcode());
        return qc ;
    }
}
